<?php
namespace app\common\controller;
use think\Controller;

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Cell\DataType;
class PhpOffice extends Controller
{
    protected $abc = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
    // $list = 要导出的数据
    // $type = 要导出的类型
    // $otherDatas = 要导出的项目（目前只有导出统筹单用得到）
    public function exportExcel($list, $type, $otherDatas = [])
    {
        $url = '';
        if( $type == 'VehicleList' )
        {
            $url = $this->_exportVehicleList($list);
        }
        if( $type == 'vehicleInTheEnd' )
        {
            $url = $this->_exportVehicleInTheEnd($list);
        }
        if( $type == 'vehicleExpired' )
        {
            $url = $this->_exportVehicleExpired($list);
        }
        if( $type == 'bill' )
        {
            $url = $this->_exportBillList($list);
        }
        
        return $url;
    }

    public function getExcelDetail($url)
    {
        $file_name = $this->_download($url);

        $obj_PHPExcel = IOFactory::load($file_name);//$encode='utf-8' //加载文件内容,编码utf-8  

        $excel_array = $obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式

        array_shift($excel_array); //删除第一个数组(标题);

        //逻辑代码

        return $excel_array;
 
    }

    // 通过curl下载网络上的文件
    private function _download($url){
        $fileName = explode('.', $url);
        $fileName = $fileName[count($fileName) - 1];

        //本地文件路径
        $local_file_path = ROOT_PATH.'public/excel/'.time().$fileName;

        //初始化CURL句柄
        $curl = curl_init();

        //设置CURL句柄
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_HEADER, 0);
        curl_setopt($curl, CURLOPT_TIMEOUT, 30);

        //执行CURL操作
        $file_data = curl_exec($curl);

        //关闭CURL句柄
        curl_close($curl);

        //将下载的文件保存到本地
        $fp = fopen($local_file_path, 'wb');
        fwrite($fp, $file_data);
        fclose($fp);

        return $local_file_path;
    }

    private function _getExcelColName(){
        $abc = $this->abc;

        for ($i=0; $i < 10; $i++) { 
            for ($j=0; $j < 26; $j++) { 
                $abc[] = $abc[$i].$abc[$j];
            }
        }
        return $abc;
    }

    //公共文件，用来传入xls并下载
    private function downloadExcel($newExcel, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        // if ($format == 'Xlsx') {
        //     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        // } elseif ($format == 'Xls') {
        //     header('Content-Type: application/vnd.ms-excel');
        // }
 
        // header("Content-Disposition: attachment;filename="
        //     . $filename . date('Y-m-d') . '.' . strtolower($format));
        // header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);
 
        // $objWriter->save('php://output');
 
        //通过php保存在本地的时候需要用到
        $url = 'excel/'.$filename.'.xlsx';
        $objWriter->save(ROOT_PATH.'public/excel/'.$filename.'.xlsx');
        return $url;
        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        
    }

    private function _setTitle($objSheet,$titleList){
        $abc = $this->_getExcelColName();
        for ($i=0; $i < count($titleList); $i++) { 
            $objSheet->setCellValue($abc[$i].'1', $titleList[$i]);
        }
    }
    private function _setValue($objSheet,$fieldList,$list){
        $abc = $this->_getExcelColName();

        for ($i=0; $i < count($list); $i++) {
            for ($j=0; $j < count($fieldList); $j++) { 
                $title = $fieldList[$j]['title'];


                if($fieldList[$j]['type'] == 'default') $objSheet->setCellValue($abc[$j]. ($i+2), $list[$i][$title]);


                if($fieldList[$j]['type'] == 'string') $objSheet->setCellValueExplicitByColumnAndRow(($j+1) , ($i+2), $list[$i][$title],DataType::TYPE_STRING);

            }
        }
    }

    private function _exportVehicleList($list)
    {
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('sheet1');  //设置当前sheet的标题
        $abc = $this->_getExcelColName();

        //设置第一栏的标题
        $titleList = ['区域','档案号','登记单位','车牌号码','姓名','身份证号','电话','地址','车辆品牌','车辆类型','车辆型号','驱动形式','车身颜色','车架号','发动机号','营运证号','登记日期','检验有效期','营运有效期','交强有效期','商业有效期','总质量','整备质量','核定质量','核定载客','发动机型号','燃料种类','排量功率','制造厂家','轮距','轮胎数','轮胎规格','轴距','外廓尺寸','货箱尺寸','状态','签合同日期','合同到期','发票金额','GPS平台','风控处理方式','备注'];
        $this->_setTitle($objSheet,$titleList);

        $fieldList = [
            [
                'title' => 'area',
                'type' => 'default',
            ],
            [
                'title' => 'danganhao',
                'type' => 'default',
            ],
            [
                'title' => 'company',
                'type' => 'default',
            ],
            [
                'title' => 'plate_number',
                'type' => 'default',
            ],
            [
                'title' => 'realname',
                'type' => 'default',
            ],
            [
                'title' => 'idcard',
                'type' => 'string',
            ],
            [
                'title' => 'tel',
                'type' => 'default',
            ],
            [
                'title' => 'address',
                'type' => 'default',
            ],
            [
                'title' => 'brand',
                'type' => 'default',
            ],
            [
                'title' => 'vehicle_type',
                'type' => 'default',
            ],
            [
                'title' => 'vehicle_model',
                'type' => 'default',
            ],
            [
                'title' => 'drive_type',
                'type' => 'default',
            ],
            [
                'title' => 'color',
                'type' => 'default',
            ],
            [
                'title' => 'vin_number',
                'type' => 'default',
            ],
            [
                'title' => 'engine_number',
                'type' => 'default',
            ],
            [
                'title' => 'business_cert',
                'type' => 'string',
            ],
            [
                'title' => 'reg_date',
                'type' => 'default',
            ],
            [
                'title' => 'check_date',
                'type' => 'default',
            ],
            [
                'title' => 'business_date',
                'type' => 'default',
            ],
            [
                'title' => 'insurance_date_c',
                'type' => 'default',
            ],
            [
                'title' => 'insurance_date_b',
                'type' => 'default',
            ],
            [
                'title' => 'weight',
                'type' => 'default',
            ],
            [
                'title' => 'full_weight',
                'type' => 'default',
            ],
            [
                'title' => 'approved_weight',
                'type' => 'default',
            ],
            [
                'title' => 'passenger',
                'type' => 'default',
            ],
            [
                'title' => 'engine_model',
                'type' => 'default',
            ],
            [
                'title' => 'fuel_type',
                'type' => 'default',
            ],
            [
                'title' => 'engine_power',
                'type' => 'default',
            ],
            [
                'title' => 'manufacturer',
                'type' => 'default',
            ],
            [
                'title' => 'wheel_track',
                'type' => 'default',
            ],
            [
                'title' => 'wheel_number',
                'type' => 'default',
            ],
            [
                'title' => 'wheel_size',
                'type' => 'default',
            ],
            [
                'title' => 'wheel_base',
                'type' => 'default',
            ],
            [
                'title' => 'outline_size',
                'type' => 'default',
            ],
            [
                'title' => 'cargo_size',
                'type' => 'default',
            ],
            [
                'title' => 'status',
                'type' => 'default',
            ],
            [
                'title' => 'contract_start',
                'type' => 'default',
            ],
            [
                'title' => 'contract_end',
                'type' => 'default',
            ],
            [
                'title' => 'invoice_value',
                'type' => 'default',
            ],
            [
                'title' => 'gps',
                'type' => 'default',
            ],
            [
                'title' => 'risk',
                'type' => 'default',
            ],
            [
                'title' => 'remark',
                'type' => 'default',
            ]
        ];
        
        $this->_setValue($objSheet,$fieldList,$list);
        
        $url = $this->downloadExcel($newExcel, time(), 'Xls');

        return $url;
    }
    private function _exportBillList($list)
    {
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('sheet1');  //设置当前sheet的标题
        $abc = $this->_getExcelColName();

        //设置第一栏的标题
        $titleList = ['到期还款日','类型','债主','车牌号码','车队','车主','电话','总金额','已收','状态'];
        $this->_setTitle($objSheet,$titleList);

        $fieldList = [
            [
                'title' => 'deadline',
                'type' => 'default',
            ],
            [
                'title' => 'cate',
                'type' => 'default',
            ],
            [
                'title' => 'grandfather',
                'type' => 'default',
            ],
            [
                'title' => 'plate_number',
                'type' => 'default',
            ],
            [
                'title' => 'company',
                'type' => 'default',
            ],
            [
                'title' => 'realname',
                'type' => 'default',
            ],
            [
                'title' => 'tel',
                'type' => 'default',
            ],
            [
                'title' => 'totalAmount',
                'type' => 'default',
            ],
            [
                'title' => 'repay_amount',
                'type' => 'default',
            ],
            [
                'title' => 'statusText',
                'type' => 'default',
            ]
        ];
        
        $this->_setValue($objSheet,$fieldList,$list);
        
        $url = $this->downloadExcel($newExcel, time(), 'Xls');

        return $url;
    }
    private function _exportVehicleExpired($list)
    {
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('sheet1');  //设置当前sheet的标题
        $abc = $this->_getExcelColName();

        //设置第一栏的标题
        $titleList = ['区域','车牌号码','车队','车主','电话','强险到期日期','商业险到期日期'];
        $this->_setTitle($objSheet,$titleList);

        $fieldList = [
            [
                'title' => 'area',
                'type' => 'default',
            ],
            [
                'title' => 'plate_number',
                'type' => 'default',
            ],
            [
                'title' => 'company',
                'type' => 'default',
            ],
            [
                'title' => 'realname',
                'type' => 'default',
            ],
            [
                'title' => 'tel',
                'type' => 'default',
            ],
            [
                'title' => 'insurance_date_b',
                'type' => 'default',
            ],
            [
                'title' => 'insurance_date_c',
                'type' => 'default',
            ]
        ];
        
        $this->_setValue($objSheet,$fieldList,$list);
        
        $url = $this->downloadExcel($newExcel, time(), 'Xls');

        return $url;
    }
    private function _exportVehicleInTheEnd($list)
    {
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('sheet1');  //设置当前sheet的标题
        $abc = $this->_getExcelColName();

        //设置第一栏的标题
        $titleList = ['区域','车牌号码','车队','车主','电话','强险到期日期','商业险到期日期'];
        $this->_setTitle($objSheet,$titleList);

        $fieldList = [
            [
                'title' => 'area',
                'type' => 'default',
            ],
            [
                'title' => 'plate_number',
                'type' => 'default',
            ],
            [
                'title' => 'company',
                'type' => 'default',
            ],
            [
                'title' => 'realname',
                'type' => 'default',
            ],
            [
                'title' => 'tel',
                'type' => 'default',
            ],
            [
                'title' => 'insurance_date_b',
                'type' => 'default',
            ],
            [
                'title' => 'insurance_date_c',
                'type' => 'default',
            ]
        ];
        
        $this->_setValue($objSheet,$fieldList,$list);
        
        $url = $this->downloadExcel($newExcel, time(), 'Xls');

        return $url;
    }

    


}
